﻿using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;

using Jungle.Code.DAL;

namespace Jungle.News
{
    public class News_Comment
    {
        private string ConnectionString = DBHelper.ConnectionString;

        public int CountQry;
        public string err;
        public List<News_Comment> CommentList;

        // Private Fields
        #region Fields
        private int commentid;
        private int postid;
        private int parentid;
        private int userid;
        private string description;
        private DateTime pubdate;
        private int level;
        private string treepath;
        #endregion

        // Properties
        #region Properties
        public int CommentId        { get { return commentid; }    set { commentid = value; } }
        public int PostId           { get { return postid; }       set { postid = value; }    }
        public int ParentId         { get { return parentid; }     set { parentid = value; }  }
        public int UserId           { get { return userid; }       set { userid = value; }    } 
        public string Description   { get { return description; }  set { description = value; } }
        public DateTime PubDate     { get { return pubdate; }      set { pubdate = value; }   }
        public int Level            { get { return level; }        set { level = value; } }
        public string TreePath      { get { return treepath; }     set { treepath = value; } }

        #endregion


        //-------------------------------------------------------------------------------------
        //  Сохраним 
        //-------------------------------------------------------------------------------------
        public void Add()
        {
            err=null;
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("news.Comment_SavTblComments", connect);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@pstid", SqlDbType.Int).Value = postid;
                command.Parameters.Add("@parid", SqlDbType.Int).Value = parentid;
                command.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
                command.Parameters.Add("@descr", SqlDbType.Text).Value = description;
                command.Parameters.Add("@pbdate", SqlDbType.SmallDateTime).Value = pubdate;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
            catch (SqlException ex)
            {
                err=ex.ToString();
            }
            finally
            {
                connect.Close();
            }
           
        }

        //-------------------------------------------------------------------------------------
        //  Удалим
        //-------------------------------------------------------------------------------------
        public void Delete()
        {
            err=null;
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("news.Comment_DelTblComments", connect);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@id", SqlDbType.Int).Value = commentid;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
            catch (SqlException ex)
            {
                err=ex.ToString();
            }
            finally
            {
                connect.Close();
            }
        }
        //-------------------------------------------------------------------------------------
        // обновим
        //-------------------------------------------------------------------------------------
        public void Update()
        {
            err=null;
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("news.Comment_UpdTblComments", connect);
                command.Parameters.Add("@CommentId", SqlDbType.Int).Value = commentid;
                command.Parameters.Add("@pstid", SqlDbType.Int).Value = postid;
                command.Parameters.Add("@parid", SqlDbType.Int).Value = parentid;
                command.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
                command.Parameters.Add("@descr", SqlDbType.Text).Value = description;
                command.Parameters.Add("@pbdate", SqlDbType.SmallDateTime).Value = pubdate;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
            catch (SqlException ex)
            {
                err=ex.ToString();
            }
            finally
            {
                connect.Close();
            }
        }
        //-------------------------------------------------------------------------------------
        // получить все записи по конкретному Комменту
        //-------------------------------------------------------------------------------------
        public List<News_Comment> GetByComment()
        {
          return GetQry("CommentId", CommentId);
        }
        //-------------------------------------------------------------------------------------
        // получить все записи по конкретному посту
        //-------------------------------------------------------------------------------------
        public List<News_Comment> GetByPost()
        {
           return GetQry("PostId", PostId);
        }
        //-------------------------------------------------------------------------------------
        // получить все записи по конкретному Юзеру
        //-------------------------------------------------------------------------------------
        public List<News_Comment> GetByUser()
        {
          return GetQry("UserId", UserId);
        }
        //-------------------------------------------------------------------------------------
        private List<News_Comment> GetQry(string fieldname, int fieldvalue)
        {
            err=null;
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("news.Comment_GetTblComments", connect);
                SqlParameter param_fieldname = command.Parameters.Add("@fieldname", SqlDbType.NVarChar, 100);
                SqlParameter param_fieldvalue = command.Parameters.Add("@fieldvalue", SqlDbType.Int);
             
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;
                param_fieldname.Value = fieldname;
                param_fieldvalue.Value = fieldvalue;

                CommentList = new List<News_Comment>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    News_Comment cmnt = new News_Comment();
                    cmnt.CommentId = Convert.ToInt32(reader["CommentId"]);
                    cmnt.PostId = Convert.ToInt32(reader["PostId"]);
                    cmnt.ParentId = Convert.ToInt32(reader["ParentId"]);
                    cmnt.UserId = Convert.ToInt32(reader["UserId"]);
                    cmnt.Description = Convert.ToString(reader["Description"]);
                    cmnt.PubDate = Convert.ToDateTime(reader["PubDate"]);
                    cmnt.Level = Convert.ToInt32(reader["level_"]);
                    cmnt.TreePath = Convert.ToString(reader["treepath"]);
                    CommentList.Add(cmnt);
                }
                reader.Close();
                command.Connection.Close();
            }
            catch (SqlException ex)
            {
                err= ex.ToString();
            }
            finally
            {
                connect.Close();
            }
            return CommentList;
        }
        //-------------------------------------------------------------------------------------
        // получить кол-во записей по конкретному посту
        //-------------------------------------------------------------------------------------
        public void GetCountByPost()
        {
           GetCountQry("PostId", PostId);
        }
        //-------------------------------------------------------------------------------------
        // получить кол-во записей по конкретному Юзеру
        //-------------------------------------------------------------------------------------
        public void GetCountByUser()
        {
           GetCountQry("UserId", UserId);
        }
        //-------------------------------------------------------------------------------------
        // получить количество строк результата запроса
        //-------------------------------------------------------------------------------------
        public void GetCountQry(string fieldname,int fieldvalue)
        {
            err=null;
            SqlConnection connect = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = new SqlCommand("news.Comment_GetCountTblComments", connect);
                SqlParameter param_fieldname = command.Parameters.Add("@fieldname", SqlDbType.NVarChar, 100);
                SqlParameter param_fieldvalue = command.Parameters.Add("@fieldvalue", SqlDbType.Int);

                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;
                param_fieldname.Value = fieldname;
                param_fieldvalue.Value = fieldvalue;

                command.Connection.Open();
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    CountQry = Convert.ToInt32(reader.GetValue(0));
                }
                reader.Close();
                command.Connection.Close();
            }
            catch (SqlException ex)
            {
                err=ex.ToString();
            }
            finally
            {
                connect.Close();
            }
        }
        //-------------------------------------------------------------------------------------
        // получить 10 последних комментариев
        //-------------------------------------------------------------------------------------
        public void GetLastTen()
        {
            err = null;

            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                try
                {
                    SqlCommand command = new SqlCommand("news.Comment_GetLastTenComment", connect);
                    SqlDataReader reader = null;
                    command.CommandType = CommandType.StoredProcedure;

                    CommentList = new List<News_Comment>();
                    command.Connection.Open();
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        News_Comment cmnt = new News_Comment();
                        cmnt.CommentId = Convert.ToInt32(reader["CommentId"]);
                        cmnt.PostId = Convert.ToInt32(reader["PostId"]);
                        cmnt.ParentId = Convert.ToInt32(reader["ParentId"]);
                        cmnt.UserId = Convert.ToInt32(reader["UserId"]);
                        cmnt.Description = Convert.ToString(reader["Description"]);
                        cmnt.PubDate = Convert.ToDateTime(reader["PubDate"]);
                        cmnt.Level = Convert.ToInt32(reader["level_"]);
                        cmnt.TreePath = Convert.ToString(reader["treepath"]);
                        CommentList.Add(cmnt);                        
                    }
                    reader.Close();
                    command.Connection.Close();
                }
                catch (SqlException ex)
                {
                    err = ex.ToString();
                }
                finally
                {
                    connect.Close();
                }
            }

        }
        //-------------------------------------------------------------------------------------
        // получить все дочерние комментарии
        //-------------------------------------------------------------------------------------
        public void GetChilds()
        {
            err = null;

            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                try
                {
                    SqlCommand command = new SqlCommand("news.Comment_GetChildComment", connect);
                    SqlDataReader reader = null;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@CommentId", SqlDbType.Int).Value = commentid;

                    CommentList = new List<News_Comment>();
                    command.Connection.Open();
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        News_Comment
                            cmnt = new News_Comment();
                        cmnt.CommentId = Convert.ToInt32(reader["CommentId"]);
                        cmnt.PostId = Convert.ToInt32(reader["PostId"]);
                        cmnt.ParentId = Convert.ToInt32(reader["ParentId"]);
                        cmnt.UserId = Convert.ToInt32(reader["UserId"]);
                        cmnt.Description = Convert.ToString(reader["Description"]);
                        cmnt.PubDate = Convert.ToDateTime(reader["PubDate"]);
                        cmnt.Level = Convert.ToInt32(reader["level_"]);
                        cmnt.TreePath = Convert.ToString(reader["treepath"]);
                        CommentList.Add(cmnt);
                    }
                    reader.Close();
                    command.Connection.Close();
                }
                catch (SqlException ex)
                {
                    err = ex.ToString();
                }
                finally
                {
                    connect.Close();
                }
            }

        }
        public int GetCountChilds()
        {
            int count = 0;
            err = null;

            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                try
                {
                    SqlCommand command = new SqlCommand("news.Comment_GetCountChildComment", connect);
                    SqlDataReader reader = null;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@CommentId", SqlDbType.Int).Value = commentid;


                    command.Connection.Open();
                    reader = command.ExecuteReader();
                    if (reader.Read())
                    {
                        count = Convert.ToInt32(reader.GetValue(0));
                    }
                    reader.Close();
                    command.Connection.Close();
                }
                catch (SqlException ex)
                {
                    err = ex.ToString();
                }
                finally
                {
                    connect.Close();
                }
                return count;
            }

        }

    }
}
